package connection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Project;

public class ProjectConnection {
	
	 public void addProjectToDB(Project oProject, int pmId){		
			
			ConnectionControl mgr = ConnectionControl.getInstance();
			Connection con = mgr.getConnection();
			
			try {
				
				Statement statement = con.createStatement() ;
				statement.executeUpdate("INSERT INTO Project(ID,NAME,DESCRIPTION, STARTDATE," +
						                 "PMID) VALUES("+oProject.getNumber()+", '"
						                 +oProject.getName()+"','s"+oProject.getDesc()+"', '"+oProject.getStartDate()+						                 
						                 "', "+pmId+")");
			    statement.close();		
				
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			mgr.closeConnection(con);
		}
	 public void removeProjectFromDB(int projectId){
			
			ConnectionControl mgr = ConnectionControl.getInstance();
			Connection con = mgr.getConnection();
			
			try {
				
				Statement statement = con.createStatement() ;			
				statement.executeUpdate("DELETE FROM Project WHERE ID = "+projectId);			
				statement.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			mgr.closeConnection(con);
		}
	 
	 public void updateProjectFromDB(int id,String name,String desc,String startDate){
			
			ConnectionControl mgr = ConnectionControl.getInstance();
			Connection con = mgr.getConnection();
			
			try {
				
				Statement statement = con.createStatement() ;			
				statement.executeUpdate("UPDATE PROJECT SET NAME='"+name+"',DESCRIPTION = '"+ desc +" ',STARTDATE='"+startDate+"' WHERE ID = "+id);
	            statement.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			mgr.closeConnection(con);
		}
	 
	 public ArrayList<Project> getAllProjectsFromDB(){
			
			ConnectionControl mgr = ConnectionControl.getInstance();
			Connection con = mgr.getConnection();
			
			ArrayList<Project> oProjectList = new ArrayList<Project>();
			
			try {
				
				Statement statement = con.createStatement() ;
				
				ResultSet rset = statement.executeQuery("select * from Project");
				
				while (rset.next()) {
					Project oProject = new Project();
					oProject.setNumber(rset.getInt(1));
					oProject.setName(rset.getString(2));
					oProject.setDesc(rset.getString(3));
					oProject.setStartDate(rset.getString(4));
					oProject.setPmId(rset.getInt(5));					
					
					oProjectList.add(oProject);
					}
				rset.close();
				statement.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			mgr.closeConnection(con);
			
			return oProjectList;
			
		}
 	 public ArrayList<Project> getAllProjectsOfPM(int pmId){
		 
			ConnectionControl mgr = ConnectionControl.getInstance();
			Connection con = mgr.getConnection();
			
			ArrayList<Project> oProjectList = new ArrayList<Project>();
			
			try {
				
				Statement stetement = con.createStatement() ;			
				ResultSet rset = stetement.executeQuery("select * from Project where pmid = "+ pmId);			
				while (rset.next()) {
					
					Project oProject = new Project();
					
					oProject.setNumber(rset.getInt(1));
					oProject.setName(rset.getString(2));
					oProject.setDesc(rset.getString(3));
					oProject.setStartDate(rset.getString(4));
					oProject.setPmId(rset.getInt(5));
										
					oProjectList.add(oProject);
					}
				rset.close();
				stetement.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			mgr.closeConnection(con);
			
			return oProjectList;
		}

}
